Previous Page TOC Next Page


35 — Creating a Stand-Alone Database Application

by Ross Rothmeier

Most applications fill a specific need. A common need is an application that enables its users to access their information the way they want. These applications usually use stand-alone databases. When expressing this need to a developer, the user might say something like, "I just want a database that keeps track of my customers."

Taking this request and converting it into an application using Visual Basic is a process that involves the following four steps:

This chapter explains in detail how to perform each of these steps.

Getting the Requirements

Whether you are developing this application for yourself or someone else, your first job as a developer is to find out what this person's needs are and map them into some kind of specification. Without a specification, it is hard to tell when a project is finished and even harder to explain to someone else that it is.

Try to find out what kind of data the potential user (the client for the sake of this project) has in mind. Names and addresses are common and pretty easy to add to a project, but there are often special things that are trivial to put in at the beginning of a project and a nightmare to add in at the end, such as a shipping charge that may change how invoicing is done. Talk with the client using a pencil and paper. Draw sample screens and reports. As you discuss what the client needs to do, keep a running list of fields and things the application should do. The following list gives some examples of how the information you get from a client turns into design considerations:

With the requirements and design considerations in mind, you will start to get an idea of the processes your client wants the computer to do for him. These processes, when mapped out, would look something like Figure 35.1.


Figure 35.1. The high-level process flow.

Designing the Database

The database is the heart of this application, so you should spend some time thinking about how the data should be arranged. Start by writing down the kinds of information you believe the application will need for the various processes. In this example application, you need information about a customer's demographics, orders, and account status. As you break down these general areas, you find that each contains a lot of specific data. List the specific pieces of data in each general category. You may find that a general category has several subcategories. By breaking the data into general categories, you are designing tables for a relational database.

Organizing Data into Tables

Organizing data into groups is the beginning of the table. Each element in a table will be a field. In general, a good relational design will not have a field appear in more than one table. Each table should also have a key.

The customer information table is probably the center of this application. It should contain demographic information and anything you will need whenever you refer to a customer, as shown in Table 35.1. 

Field


Data type


Length


Comments

CustomerNum

Counter

16

Primary key, ascending

First name

Text

16


Last name

Text

20


Company name

Text

50


Street

Text

30


P.O. Box

Text

5


Suite/apartment

Text

30


City

Text

16


State

Text

2


Zip code

Text

10


Primary phone

Text

14


Secondary phone

Text

14


Contact person name

Text

10


Current balance

Currency

16

This value will be referred to frequently in the application

Note

Performance and a perfect database design are often trade-offs. Because you are targeting a relatively slow computer, the current balance will be a stored value in the main customer table. A more pure design approach would be to calculate the current balance as needed and store it in a variable, but this approach could cause a performance bottleneck when calculating balances for a screen full of customers.

In this case, the trade-off was made for performance on another screen where the current balance and the order details for all customers are displayed at the same time. Loading the screen and calculating the current balance for each customer took longer as the number of customers and orders increased, but using a precalculated current balance did not result in performance degradation.

The next major group of information has to do with orders. The client needs to know who bought what, when the customer bought it, how much the client charged, and how much the customer paid. Table 35.2 demonstrates how you might organize this information into a table. 

Field


Data type


Length


Comments

CustomerNum

Number

Long

Key field

Date

Date


Date of delivery

Description

Text

35


Quantity

Number

Double


Charge

Number

Double

Money the customer owes

Credit

Number

Double

Money the customer paid

OrderNum

Counter


Primary key

Creating Tables for Reporting

Reports are not always a developer's favorite part of an application. Keeping track of pages, lines, alignment, and fonts requires a lot of work. You can write your own reports using Visual Basic. If, however, you want to save a ton of work and put out some pretty snazzy reports, use the Report Designer (also known as Crystal Reports for Visual Basic, Crystal Reports, or just plain Crystal).

Note

All examples and discussion of Crystal Reports are based on the version 3 add-in shipped with Visual Basic 95.

Crystal makes report development easy, but using it can get tricky if you don't set up the tables for easy reporting. Add Tables 35.3, 35.4, 35.5, and 35.6 to your application. The reason for these tables will become apparent when you create the reports. 

Field


Data type


Length


Comments

Customer number

Numeric

Long

First part of primary key

Order number

Numeric

Long

Second part of primary key

Invoice date

Date

 



Field


Data type


Length


Comments

OrderNum

Numeric

Long

Key, and first part of primary key

Counter

Counter

Long

Second part of primary key

Product

Text

30


Quantity

Numeric

Integer


Price

Currency



Subtotal

Currency



Tax

Currency



Deposit

Currency



Total

Currency

 



Field


Data type


Length


Comments

CustomerNumber

Numeric

Long

Key field

CompanyName

Text

50


LastName

Text

20


FirstName

Text

16


Address

Text

30


Apt

Text

30


POBox

Text

5


City

Text

16


State

Text

2


Zip

Text

10


StatementDate

Date



CompanyGreeting

Text

80

 


Field


Data type


Length


Comments

CompanyName

Text

50


Address1

Text

50


Address2

Text

50


City

Text

32


State

Text

50


Zip

Text

10


Phone

Text

14


FAX

Text

14


CompanySlogan

Text

50


Picking a Primary Key

Even if you are careful to properly group data in each table, you may find that there is no elegant way to uniquely identify a customer. This identification is very important in a relational database because a unique key is the common thread that connects two or more tables. You may think that you can easily create a good key using some or all of the information you have about the customer. This approach, however, is likely to generate a large, multifield key (which slows performance) and depend on the user entering certain information about every customer he has (and sometimes you don't know everything about a customer).

The main customer table should have a unique key to identify customers so that the program can retrieve a specific customer's information. The order table should have a unique key so that the program can retrieve a unique order. The best approach to defining a primary key in these cases is to assign an arbitrary number to each customer and order. The Customer table has a field called CustomerNum, and the Order table has a field called OrderNum. When you create these tables, you assign these fields the special attribute of counter, which is a long integer with some special properties. This attribute causes Visual Basic to assign a unique key each time a record gets added to the database. Getting Visual Basic to perform this task requires a little code, which is shown and explained in a later section called "Adding a Customer Record."

Tying Tables Together with Keys

Keys are the threads that tie all the tables in a relational database together. Keys are also required when using certain data constructs (like snapshots) that this application uses. For example, you will be retrieving customer orders through the relationship of CustomerNum in the CustMain table and the Orders table. This field must have the exact same name and data type in each table in order for the relationship to work. Keys are also helpful, making the database application easy to maintain and understand by making it more consistent and showing (graphically, if you are using Microsoft Access) how the data is related.

Creating the Database Using the Data Manager

Visual Basic comes with a very powerful add-in called the Data Manager. Using the Data Manager, you can create and modify the database. You can also add and modify the data in it.

Note

Even though the Data Manager is an easy tool to use, you may find that using Microsoft Access is an easier way to create a database for a Visual Basic application. Both tools use the same principles, but Microsoft Access has many useful features that the Data Manager does not have, such as the database documenter, the ability to view multiple tables at one time, and a great SQL builder. But if you just want to create a database, the Data Manager is quite capable of the task.

Using the Data Manager, create the tables you outlined in the design. After creating the tables, activate the counter attributes for the counter fields, as shown in Figure 35.2.


Figure 35.2. Making CustomerNum a counter field in CustMain.

After setting the counter attribute, make this field the primary key by editing the field properties and adding an index, as shown in Figure 35.3.


Figure 35.3. Setting the primary key for CustMain.

Developing a Functional Prototype

With a list of requirements from the client, the process flow diagram, and a database design, you have enough material for a specification that shows what this application will do and how it will do it. With this specification, you are ready to construct a database and a functional prototype.

A prototype is a model of the application. If you have a database to work with, you may even be able to put some real working features in your prototype without writing any code. You should avoid writing code for a prototype whenever possible. If you want to show a feature that will require code, just put together a few forms that convey the idea and use a function key to display them. Don't get too attached to a prototype. You will be changing it.

Adding a Startup Screen

Using the process flow as a roadmap, start designing the screens needed to complete a process. One thing the process flow did not show that the user will need is a place to start. To help the user out, add a startup screen called Cover_scr.

A startup screen helps organize an application, and it is a convenient place to put initialization routines and things like a screen for typing in who the company is and a way to exit the application. The startup screen (Cover_scr.frm) for this application allows the user to do the following:

The Cover_scr is where the users can select which part of the application they want to use. This design allows you to add features to the application later on by adding a button. The two features you will add in the beginning will give access to customer information and to general account information. Table 35.7 lists the properties of the Cover_scr form. 

Property


Setting


Comments

Appearance

1-3d


Name

Cover_scr


Background Color

&H00C0C0C0& (light gray)

For 16-bit VGA, this is a safe color.

Maximize

False

In a 640´480 world, the controls will not look centered on the screen if you allow the user to change the size of the window. There are elastic custom controls that can solve this problem.

Icon

(Icon)

Because this form is also the startup form (see Figure 35.5), this form's icon is the icon that will be used for the program in the program group.


Figure 35.4. The process flow with a starting place.


Figure 35.5. The Cover_Scr form.

To make the Cover_scr form the starting form for the application, select Tools | Options and click on the Project tab. The first field on the Project screen is a drop-down list of all the forms in the project (see Figure 35.6).


Figure 35.6. Setting the Cover_scr as the startup form.

Putting a version number on a startup screen can be helpful when you start modifying the application. You know as soon as you start the application what version you are running. This version number is also helpful if a user ever calls you about the software. If you have updated the software, you will want to know what version that person is using. You can add the version number to the screen by inserting a label and specifying the version number in the label's Caption property.

Graphics can make a screen more approachable. Putting a company logo in the center of the startup screen adds a professional touch. The graphic on Cover_Scr is a picture box control with a graphic (rolodex.wmf from the Windows Meta Files (.WMF) included with Visual Basic 95 to be exact).

Most Windows applications have a menu bar even if there are on-screen buttons that do the same thing. A menu bar is part of the standard look and feel of a Windows application. You don't need to add code to the menu bar at this time, but the menu bar completes the look of the startup form so the client will get an idea of what the final product will look like. To create a menu bar, choose Tools | Menu Editor. Figure 35.7 shows the menu for Cover_scr.


Figure 35.7. The menu bar for Cover_scr.

The Prototype Customer Information Screens

The first thing your client talked about was getting at customer information. Figure 35.8 highlights this portion of the specification you created earlier in the chapter.


Figure 35.8. The processes related to a specific customer.

In a prototype, you are trying to convey how this application will look and how the user will go from one screen to another. From the Cover_scr, a user presses the Customer Files (btnCustMain) to display the Customer Information screen (see Figure 35.9). A user will also expect to see the pointer turn into an hourglass if this process takes a few seconds (which it probably will on that 486SX). Even though the prototype should have a minimum amount of code in it, you will need code to display screens. Entering the code in Listing 35.1 for each button makes the prototype much easier to demonstrate; you also will need this code in the final version of the application. Table 35.8 lists the btnCustMain properties.


Figure 35.9. The Customer Information (CustInf) form. 

Private Sub btnCustMain_Click()

    MousePointer = 11 ' hourglass

    Custinf.Show

End Sub
Property


Setting


Comments

Name

btnCustMain

This is an SSCommand control.

Caption

&Customer Files

The &C in Customer makes Alt+C the key combination for this button.

Picture

(Icon)

This icon is folder05.ico from the Office subdirectory.

The main customer information screen answers more of the requirements than any other screen. In this screen, the user enters, changes, and deletes customer information. The user can also enter a note about the customer in this form and see the current customer balance. Table 35.9 lists the properties for this form. 

Property


Setting


Comments

Name

CustInf

A plain old form.

KeyPreview

True

Allows the form-level KeyPress event to preview keystrokes before the controls do.

MaxButton

False

Keeps the form from getting larger than it was designed to.

The buttons on the Customer Information screen display other screens of information about the customer. For example, if the user clicks the Account (btnAccount) button, a Customer Account screen appears (see Figure 35.10). Table 35.10 lists the properties for the Account button.


Figure 35.10. The Customer Account (frmCusta.frm) form. 

Property


Setting


Comments

Name

btnAccount

CommandButton object.

Caption

&Account

Establishes Alt+A as the key combination.

Tabstop

False

The user can tab through data entry fields only.

The Customer Account form contains a data-bound grid control. To access the database using the grid, you must first add a data control on the form. Table 35.11 lists the properties for the Orders data control. When bound to the data control, the grid will have access to the field names and data types in the underlying recordset. 

Property


Setting


Comments

Name

Orders

Data control object.

Caption

Orders

Makes the control easier to recognize on the screen.

DatabaseName

C:\VBPROJ\SAMS\VB4DB.MDB

This is the default name. You can assign another by changing the Database_name variable in the Cover_scr load subroutine.

Recordsource

Orders

Table in the database to bind to.

RecordsetType

1-Dynaset

This grid will make changes to the underlying table.

Visible

False

The default property is True.

In order to add the data-bound grid to the form, the grid must be in the toolbox. If it is not, you must add it to the project. Choose Insert | Custom Controls. In the Custom Controls dialog box (see Figure 35.11), you can select which controls you want to be active in your project. After adding the grid to the form, configure its properties as listed in Table 35.12.


Figure 35.11. Adding the data bound control to the project.

Tip

If you use particular custom controls frequently, add them to your AUTO16LD.VBP file for 16-bit environments or AUTO32LD.VBP file for 32-bit development environments. 
Property


Setting


Comments

Caption

Account Activity


ColumnHeaders

True

Puts the field names at the top of the columns.

DataMode

Bound


DataSource

Orders

The Orders data control.

Name

OrdersGrid

This is a data-bound grid.

TabIndex

4


TabStop

True


Visible

True


To print the current status of a customer account, you will use a report. To create the Print Account Statement button in the Customer Account screen, add the Crystal Reports control to the project the same way you added the grid for the Account Activity control. Using a word processor, you can create a mock report to show how the account statement will look (see Figure 35.12). Clicking the Close button should take the user back to the Customer Information screen.


Figure 35.12. Prototype statement of account.

From the Customer Information screen, the user can click the Orders (btnOrder) button to display the Place Order screen shown in Figure 35.13. Table 35.13 lists the properties for this button. The user places an order from the current customer on the Place Order screen by typing it into the grid labeled Current Order.


Figure 35.13. The Place Order screen. 

Property


Setting


Comments

Caption

&Order

Specifies Alt+O as the key combination.

Name

btnOrder

Command button.

TabStop

False

Only tabs through data entry fields.

To print the order, the user clicks the Print This Order button. The actual order form will be developed using the Report Manager (Crystal Reports version 3 is included with the Visual Basic's professional edition). You must place a Crystal Reports control on the form. This control is not required for a prototype, but having it on the form will affect the time it takes to load the form. Adding the control now gives you an idea of how long this screen will take to paint. Set the control's Name property to rptOrderTicket.

Printing the order is not necessary in the prototype, but showing how the final order form will look is. Using any word processor, you can create an order form. As you create the mock order form, you may want to make note of the fields you will be using from the report table.

If the user clicks the Find Customer (btnFindCust) button on the Customer Information screen, a Lookup Customer screen appears (see Figure 35.15). Table 35.14 lists the properties for this button.


Figure 35.14. The prototype order printout.


Figure 35.15. The Lookup Customer form. 

Property


Setting


Comments

Caption

&Find Customer

Sets Alt+F as the key combination.

Name

btnFindCust

Command button.

TabStop

False

Only tabs through data entry fields.

The user selects the customer for the Customer Information screen from the Lookup Customer form. This form is really two forms in one. Several of the controls start out invisible when the form is first shown (lstMultiMatch and the middle OK button). The user types in a value in the Find text box, selects a field to search on from the lstSearch list box, and clicks OK (only one OK button will be displayed at run time). If the application finds more than one match to the user's selection criteria, then the multimatch list box of this form is activated. This functionality all has to be programmed later. The essential message to the user here is that this is a lookup screen. The fields you will be searching on are Customer Number, Address, Company, or Last Name.

The Prototype General Accounts Screen

The second part of this application's specification relates to account information, as shown in Figure 35.16.


Figure 35.16. The processes related to all accounts.

The General Accounts process starts back at the Cover_Scr, or startup screen when the user clicks on the Accounts (btnAccounts) button. Table 35.15 lists the properties for this button. By adding the code in Listing 35.2, you can cause the Account form to appear on-screen. 

Property


Setting


Comments

Name

btnAccounts

This is an SSCommand control.

Caption

&Accounts

This specifies Alt+A as the key combination for this button.

Picture

(Icon)

This is books04.ico from the Writing subdirectory.

Private Sub btnAccounts_Click()

    MousePointer = 11 'hourglass

    frmAccounts.Show

End Sub

Seeing all of the customer information at once is important to the client so he gets an overview of the accounts. The Accounts form (see Figure 35.17) is designed to show details about the accounts on the left-hand side of the screen and the current balance of each account on the right-hand side. By using two grids, you can show both pieces of information. You will have to write code to synchronize the two grids. You can reuse the statement of account report from the customer account section of the application.


Figure 35.17. The Accounts form.

Eventually, there may be a lot of customer records on this screen. To help the user find a particular customer number, there is a data entry field at the bottom of the screen to search for a specific customer number.

Other Screens

The Cover_scr form has two other buttons on it—Company Information and Exit. There is also a picture box in the middle of the form that you will use to display an About box that shows all the legal stuff and the author's logo. These elementary forms should also be discussed in a prototype demonstration.

Coding and Testing the Application

While showing the prototype, your client may come up with other requirements and features. Before coding and testing an application, make sure you have designed the screen and discussed the things that will happen when each button is pressed, each field is changed, and all of the major features are discussed so that you both understand how the application will look and work. If this process requires several discussions, you may be able to develop parts of the application that have remained stable, but there is a risk that a yet unknown feature may require modification to anything you do.

Creating the Startup Code

The Form Load event is a good place to put code that changes the orientation of the form because this event is only executed when the form is first displayed. Most other form-based code is in the Activate event, which is invoked whenever the form becomes the active window. If something changes the data that an inactive form is displaying, the Activate event can refresh the data (or just the controls) without having to reload the form. Code in this event improves performance by reducing the amount of screen painting needed to update data. If the user moves the form while running the application, the form remains where he put it until it is unloaded.

Because Cover_scr is the starting point for the application (you set it up as the startup form earlier), you can use the load routine to check the working environment and database to make sure everything is in order. If there is a trappable, correctable error, it can be handled here (see Listing 35.3): 

Private Sub Form_Load()

    ...

    If Not Database_name Then

        Database_name = "\vbproj\sams\vb4db.mdb"

    End If

    On Error GoTo Error_db

' Open single user (default).

    Set CustDB = OpenDatabase(Database_name)

    Exit Sub

Error_db:

    Select Case Err

        Case 3049 ' Possible corrupt database

            errmsg = Err.Description & "  To attempt repairing the database, press OK.  

To Abort, press CANCEL"

            response = MsgBox(errmsg, vbOKCancel, "Database Error")

            If response = vbOK Then

                MousePointer = 11

                DoEvents

                Cover_scr.Print "Re-indexing tables..."

                RepairDatabase Database_name

                Cover_scr.Print "Optimizing tables..."

                CompactDatabase Database_name, "\tmpdb.mdb"

                Cover_scr.Print "Resetting tables..."

                Kill Database_name

                Name "\tmpdb.mdb" As Database_name

                Cover_scr.Refresh

                MousePointer = 0

                Resume

            End If

        Case Default

            errmsg = Err.Description & "  Press Yes to continue anyway (could be risky), 

No to exit.  Continue anyway?"

            response = MsgBox(errmsg, vbYesNo + vbDefaultButton2, "Database Error")

            If response = vbYes Then

                Resume ' Attempt to continue

            Else

                End ' Shut down the application

            End If

    End Select

End Sub
Note

You also can perform error handling by writing and calling a central error handler. The programmer's decision becomes Do I centralize all errors or put code related to a particular activity in one place? In this case, and throughout this sample application, all errors are handled in the routine in which they can occur. This method makes all properties of the Err object available to you when you are writing damage control code. If you choose to write a central error handler, you will have to save the Err object properties in variables and pass them to the error handler.

To make code maintenance easier, each menu item calls the corresponding button's Click event subroutine. That way, if anything special has to happen when a screen is displayed, all of the code is in one place. For example, the Customers item on the Edit menu calls the Click subroutine for the btnCustMain button. Listing 35.4 gives the code for this action. 

Private Sub mnuEditCustomers_Click()

    Call btnCustMain_Click

End Sub

Private Sub btnCustMain_Click()

    MousePointer = 11 ' hourglass

    Custinf.Show 1 'modal

End Sub

The only minor exception to this rule is the About this application item on the Help menu. This item calls the Click event subroutine for the picture box rather than a button.

Whenever the cover_scr is activated, the pointer is reset to the default state for the form. This resetting allows you to set the cursor to be an hourglass when you unload a form, so the user knows something is happening. You can then turn the cursor back into a pointer when the unload is complete and the new form is reactivated. The Activate event on all forms in this sample application has this feature. Listing 35.5 provides the necessary code. 

Private Sub Form_Activate()

    ' Reset to the default  pointer when returning to this form.

    MousePointer = 0

End Sub

Adding a Customer Record

The user can add a new customer to the database by clicking the Add Customer button (or using the menu) in the CustInf form. Adding a customer to the database means adding a unique customer number to the CustMain table. Because the customer number is a counter field, you just look for an unused number or increase the last number in the file by one by entering the following code: 

Private Sub btnAddCust_Click()

    ' Look in the customer table for an unused customer number.

    Set CustMainTbl = CustDB.OpenTable("CUSTMAIN")

    CustMainTbl.Index = "PrimaryKey"

    indexval = 1

    CustMainTbl.Seek "=", indexval

    While (CustMainTbl.NoMatch = False)

        indexval = indexval + 1

        CustMainTbl.Seek "=", indexval

    Wend

Once the number has been established, you can add a record to the CustMain table, put the new customer number into the key field, and update the file: 

    ' Add this new customer to the customer table

    CustMainTbl.AddNew

    CustMainTbl.Fields("CustomerNum") = indexval

    CustMainTbl.UPDATE

After adding the record, you make it the current record: 

    ' Make this new customer number the current customer_number

    Customer_number = indexval

    txtCustomerNum.TEXT = Customer_number

    ' Make this newly created record our current record

    Qcriteria = "SELECT * FROM CUSTMAIN WHERE CustomerNum = " & Customer_number

    Set CustmainDynaset = CustDB.CreateDynaset(Qcriteria)

The CustmainDynaset is a global dynaset that is referenced by the Orders and CustAccount forms.

Tip

Make your database access and manipulation more efficient by using SQL (Structured Query Language). SQL saves code and runs much faster than the traditional approach of reading all of the records and doing the manipulations yourself by using edit and update methods on recordsets.

Finding an Existing Customer Record

A user can look up an existing customer by clicking on the Find Customer button in the CustInf form. Clicking this button displays the Lookup form. This form is actually two forms in one. It is both the form for entering selection criteria for finding a customer, and the form for selecting a specific customer if more than one customer matches the given criteria.

The lstSearch combo box lists the criteria the user may type to find a customer. This list is set in the Form_Load event subroutine. Using the AddItem method, you add the Customer Number, Address, Company, and Last Name fields from the database.

Most users prefer to use the Enter key to move between fields on a form. To accommodate this, all of the text boxes on this form have Keypress event subroutines that convert a press of the Enter key to a tab. These subroutines move the focus to the control with the next highest tab index: 

If KeyAscii = 13 Then

        SendKeys "{tab}"

        KeyAscii = 0

    End If

In the case of the combo box control, the user presses the Enter key to select an item from the list. If you just mapped the keypress to be a tab, the only way a user could select an item from the list would be with a mouse click, which is unintuitive. If the user tried to press Enter to select a highlighted item, the cursor would jump to the next control on the form. To prevent this situation, use the KeyDown event to trap the keypress and force the cursor to move to the next field on the screen, as shown in Listing 35.6. 

Private Sub lstSearch_KeyDown(KeyCode As Integer, Shift As Integer)

    If KeyCode = KEY_RETURN Then

        btnFindCust(0).SetFocus

    End If

End Sub

Once the user has entered the selection criteria, you need an event that indicates that the user is finished and it is time to use that criteria to search the database. The Lookup form has two OK buttons when viewed in Design mode. Only one of these buttons will be visible at run time. The leftmost OK button (named btnFindCust) is visible when the form is initially displayed (see Figure 35.18). This button is used to find a customer that matches the selection criteria. Clicking on this button is the event that begins the database search.


Figure 35.18. The initial Lookup form.

The second OK button (named btnOK2) is used to select a specific customer if more than one matched the selection criteria (see Figure 35.19).


Figure 35.19. The Multimatch Lookup form.

The btnFindCust (the left OK) Click subroutine determines which field the user typed as the selection criteria, converts the value in the txtCustID field to the appropriate data type, and then calls the FindCust function.

The FindCust function uses SQL to select a record (or records) from the CustMain table, puts the records in the CustmainDynaset, and returns an integer (interpreted as True or False) to indicate whether any records match the selection criteria. Listing 35.7 shows the code that makes this sequence happen. 

Private Function FindCust(ByVal CustID As String, SrchField) As Integer

    'If there is no active record,

    'then return without doing anything

    If Len(CustID) = 0 Or Len(SrchField) = 0 Then

        FindCust = False

        Exit Function

    Else

        ' Set one criteria if the user is searching by Number

        If SrchField = "CustomerNum" Then

            Qcriteria = "SELECT * FROM CUSTMAIN WHERE " & SrchField & " = " & CustID & ";"

        Else

        ' Set a different criteria for an non-numeric searches

            Qcriteria = "SELECT * FROM CUSTMAIN WHERE " & SrchField & " LIKE """ & CustID & """;"

        End If

        ' Create the dynaset

        Set CustmainDynaset = CustDB.CreateDynaset(Qcriteria)

        ' Check for a successful record search.

        ' Populate the fields on the screen if a record is found.

        If CustmainDynaset.RecordCount = 0 Then

            ' Could not find the customer,

            ' so reset the Dynaset back to the original customer.

            ' If there was no original customer, then just get out.

            If Len(tmpCustNum) = 0 Then

                FindCust = False

                Exit Function

            End If

            Qcriteria = "SELECT * FROM CUSTMAIN WHERE CustomerNum" & " = " & Customer_number & ";"

            Set CustmainDynaset = CustDB.CreateDynaset(Qcriteria)

            FindCust = False

        Else

            ' Get to the last record so we can determine how

            ' many matches there were.

            CustmainDynaset.MoveLast

            ' Found more than 1 match

            If CustmainDynaset.RecordCount > 1 Then

                Customer_number = tmpCustNum

                FindCust = CustmainDynaset.RecordCount

            Else

                ' Found only 1 match

                Customer_number = CustmainDynaset.Fields("CustomerNum")

                FindCust = True

            End If

        End If

    End If

End Function

You can interpret the integer that the FindCust method returned in three ways:

Now that the Lookup form is the Multimatch form, the same basic things happen as before except that you do not have to search the CustMain table for any selection criteria.

The lstmultimatch combo box contains the list of matches to the query. To make it easier to fit on a screen, only the customer's number, first name, last name, company name, and address are displayed in this list: 

        'Make sure the list empty before adding items to it.

        lstmultimatch.Clear

        ' Go to the beginning of the dynaset and populate the list.

        CustmainDynaset.MoveFirst

        ' Put a default value in the control

            addcriteria = Str$(CustmainDynaset.Fields("CustomerNum"))

        If IsNull(CustmainDynaset.Fields("First_Name")) <> True Then

            addcriteria = addcriteria & ", " &

            _CustmainDynaset.Fields("First_Name")

        End If

        If IsNull(CustmainDynaset.Fields("Last_name")) <> True Then

            addcriteria = addcriteria & " " &

            _CustmainDynaset.Fields("Last_Name")

        End If

        If IsNull(CustmainDynaset.Fields("Company")) <> True Then

            addcriteria = addcriteria & ", " &

            _CustmainDynaset.Fields("Company")

        End If

        If IsNull(CustmainDynaset.Fields("Address")) <> True Then

            addcriteria = addcriteria & ", " &

            _CustmainDynaset.Fields("Address")

        End If

        lstmultimatch.TEXT = addcriteria

        ListCount = 0

        While CustmainDynaset.RecordCount > ListCount

            addcriteria = Str$(CustmainDynaset.Fields("CustomerNum"))

            If IsNull(CustmainDynaset.Fields("First_Name")) <> True Then

                addcriteria = addcriteria & ", " &

                _CustmainDynaset.Fields("First_Name")

            End If

            If IsNull(CustmainDynaset.Fields("Last_name")) <> True Then

                addcriteria = addcriteria & " " &

                _CustmainDynaset.Fields("Last_Name")

            End If

            If IsNull(CustmainDynaset.Fields("Company")) <> True Then

                addcriteria = addcriteria & ", " &

                _CustmainDynaset.Fields("Company")

            End If

            If IsNull(CustmainDynaset.Fields("Address")) <> True Then

                addcriteria = addcriteria & ", " &

                _CustmainDynaset.Fields("Address")

            End If

            lstmultimatch.AddItem addcriteria

            CustmainDynaset.MoveNext

            ListCount = ListCount & 1

        Wend

    End If

End If

Once the user selects an item from the list, you call the FindCust routine to fill in the CustmainDynaset again: 

' Get the customer number from the listbox text

srchCustNumstr = Mid$(lstmultimatch.TEXT, 1, custnumlen)

srchCustNum = Val(srchCustNumstr)

' Use the customer number to create the dynaset.

foundcust = FindCust(ByVal srchCustNum, "CustomerNum")

If foundcust = True Then

    MousePointer = 0

    Unload frmLookup

    Exit Sub

Else

Displaying Customer Information on the Screen

Putting values from a populated CustmainDynaset onto the screen is done though the form's Activate subroutine. Whenever the form is activated, the UpdateInfo subroutine updates all the fields in the dynaset based on the current customer number. This process is fairly simple, but be careful not to put a null value on the screen—referencing a null value will generate a run-time error. To avoid this problem, the UpdateInfo routine explicitly checks for a null value in the dynaset field. If there is a null value, the field is given a blank value: 

Private Sub UpdateInfo()

     ...

        'Check for existence of data in the fields to

        ' avoid the invalid use of null error.

        If IsNull(CustmainDynaset.Fields("First_Name")) = False Then

            txtFirstName = CustmainDynaset.Fields("First_Name")

        Else

            txtFirstName.TEXT = ""

        End If

     ...

Changing Customer Information

The Customer Information screen is based on the CustmainDynaset, but changes to the data are updated in the database as soon as the cursor leaves the field. Each field on the CustMain form has a LostFocus event subroutine that calls the UpdateField subroutine, as shown in Listing 35.8. 

' This subroutine Updates any field in the CUSTMAIN table.

Private Sub UpdateField(ByVal FldText As String, fldName As String)

Dim SqlUpdateDynaset

    ...

    If Len(FldText) <> 0 Then

        ' Change the value in the table field

        SqlUpdateDynaset = "UPDATE CUSTMAIN SET " & fldName & " = """ & FldText & """"

    Else

        ' Set the value in the table field to blank

        SqlUpdateDynaset = "UPDATE CUSTMAIN SET " & fldName & " = NULL"

    End If

    ' Put the rest of the update string on.

    SqlUpdateDynaset = SqlUpdateDynaset & " WHERE CustomerNum = " & Customer_number

    CustDB.Execute SqlUpdateDynaset

End Sub

An SQL update statement is used to modify the field using the Execute method on the database. This technique is so fast that even 386SX machines show no noticeable delay when updating the field. There are a few pitfalls to using SQL in this case, however; the following list explains how to avoid them.

Similar rules apply to apostrophes. Examples of how to handle quotes of all kinds are in the btnFindCust_Click and FindCust routines in the sample application for this chapter.

Placing an Order

The Order screen has a lot of controls on it, including two data-bound grids. The longest potential response time in the entire application is the Activate event on this form. In addition to the hourglass pointer, you give the user a little information about what is going on by displaying a panel with a special status message on it, as shown in Figure 35.20. This panel is invisible by default. It is set to visible at the beginning of the Activate subroutine, and then set to invisible again at the end of the Activate routine.


Figure 35.20. pnlDelivMsg.

The only grid that gets data from the database when the form is loaded is the Product Order History grid. The grid is bound to the data control that is bound to the CustProdHist table. To subset the data in the data controls dynaset, modify the RecordSource property using an SQL statement: 

    ' Set up the Product history data control

    SQLCustProdHistInq = "Select * from CustProdHist where CustomerNum = " & Customer_number & ";"

    CustProdHist.RecordSource = SQLCustProdHistInq

    CustProdHist.Refresh

The Current Order grid is tied to the CustThisOrder table, which should be cleared out (just in case there were any records left from a prior order). You should also put in a new record for this customer to ensure that there is a current record if the user starts entering data in the grid: 

' Clear out any old orders

CustDB.Execute ("Delete * from CustThisOrder")

' Insert a record in the CustThisOrder.

Set CustThisOrderTbl = CustDB.OpenTable("CustThisOrder")

CustThisOrderTbl.AddNew

CustThisOrderTbl.Fields("CustomerNum") = Customer_number

CustThisOrderTbl.UPDATE

CustThisOrder.Refresh

As the user types information into the Current Order grid, data is automatically put into the CustThisOrder table in the database. The only information on the screen is for the current customer. The customer number is not on the grid, but it is a field in the underlying table. You use the BeforeUpdate event to write the customer number into the CustThisOrder table, as shown in Listing 35.9. Every time the user updates data in the grid, this routine is invoked. 

Private Sub ThisOrder_BeforeUpdate(Cancel As Integer)

     CustThisOrder.Recordset.Fields("CustomerNum") = Customer_number

End Sub

Once the user has typed in the order, he can print an order form showing the order. Clicking the Print this Order button kicks off the ordering process. The Click event could take a few seconds to update the tables and load Crystal Reports. To keep the user informed, a status message is placed over the buttons. Listing 35.10 shows the code for this message. 

Private Sub btnPrintOrder_Click()

     ...

    lblPrntMsg.Left = 4200

    lblPrntMsg.TOP = 3620

    lblPrntMsg.Visible = True  puts a message on the screen saying the Print Engine is loading

    btnPrintOrder.Visible = False

    btnDelTickets.Visible = False

While this message is on the screen, four tables are updated before the Crystal Reports object is loaded: one for order history, one for accounting, and two for the report. The report needs two tables because the order may have multiple items. The first table is for information about the order; the second is for information about each item in the order. The report itself links these tables at run time. Linking these tables makes it possible to print a report with multiple-record detail sections and a one-record header.

As the orders table is updated, the primary key field OrderNum (which is defined as a counter) is automatically incremented by the database engine (Jet). In order to retrieve this number, you search for the highest number in the table. This number represents the most recent order: 

' Add a record to ORDERS.  This must be done before the CUSTORDMST

' record is added because an order number is needed.

OrdersTbl.AddNew

OrdersTbl.Fields("CustomerNum") = Customer_number

OrdersTbl.Fields("Date") = txtDelDate.TEXT

OrdersTbl.Fields("Description") = CustThisOrder.Recordset.Fields("Product")

OrdersTbl.Fields("Charge") = CustThisOrder.Recordset.Fields("Total")

OrdersTbl.UPDATE

' Now retrieve the order number of the order just placed in ORDERS

OrdersTbl.Index = "PrimaryKey"

OrdersTbl.Seek "<=", 9999999

The reporting tables are updated until there are no more detail items in the CustThisOrder table: 

' Populate the CUSTORDMST table

CustOrdMstTbl.AddNew

CustOrdMstTbl.Fields("CustomerNum") = Customer_number

CustOrdMstTbl.Fields("InvoiceDate") = txtDelDate.TEXT

CustOrdMstTbl.Fields("OrderNum") = OrdersTbl.Fields("OrderNum")

CustOrdMstTbl.UPDATE

' Populate the CUSTORDDTL table.  One record for each record contained

' in CUSTTHISORDER

CustThisOrderTbl.MoveFirst

While (CustThisOrderTbl.EOF = False)

    CustOrdDtlTbl.AddNew

    CustOrdDtlTbl.Fields("OrderNum") = OrdersTbl.Fields("OrderNum")

    CustOrdDtlTbl.Fields("product") = CustThisOrderTbl.Fields("product")

    ...

    CustOrdDtlTbl.Fields("Total") = CustThisOrderTbl.Fields("Total")

    CustOrdDtlTbl.UPDATE

Although most people are very careful to make sure their hard drives are free of unnecessary data, even the most diligent user would find it tedious to monitor the Order History table and delete old records for every customer in the database. To help out, a small piece of code keeps the Order History table from taking over the user's hard drive: 

       ' Now add a record to CustProdHist.  To keep the the filesize

       ' down, limit the history to the last 10 records.

       If (Flag = False) Then

           ' "Flag" is False which means I have not yet checked to see if

           ' there are 10 unique orders for the current customer.

           buffer = "SELECT DISTINCT OrderNum, CustomerNum, Date_Delivered FROM

           _CustProdHist WHERE "

           buffer = buffer & "((CustProdHist.CustomerNum = " &

           _Str(Customer_number) & ")) "

           buffer = buffer & " Order By Date_Delivered"

           Set dynset = CustDB.CreateDynaset(buffer)

           If (dynset.RecordCount > 0) Then

               dynset.MoveLast

           End If

           If (dynset.RecordCount >= 10) Then

               dynset.MoveFirst

               deldate = dynset.Fields("Date_Delivered")

               If deldate Then

                   buffer = "Delete * From CustProdHist Where CustomerNum =

" & Str(CustProdHistTbl.Fields("CustomerNum")) & " AND "

                   buffer = buffer & "Date_Delivered = " &

                   _dynset.Fields("Date_Delivered")

                   CustDB.Execute (buffer)

               Else

                   buffer = "Delete * from CustProdHist where CustomerNum =

" & Str(CustProdHistTbl.Fields("CustomerNum")) & " AND "

                   buffer = buffer & "product = NULL"

                   CustDB.Execute (buffer)

               End If

           End If

           Flag = True

       End If

       CustProdHistTbl.AddNew

       CustProdHistTbl.Fields("CustomerNum") = Customer_number

       CustProdHistTbl.Fields("Date_Delivered") = txtDelDate.TEXT

       CustProdHistTbl.Fields("product") = CustThisOrderTbl.Fields("Product")

       CustProdHistTbl.Fields("Qty") = CustThisOrderTbl.Fields("Qty")

       CustProdHistTbl.Fields("OrderNum") = OrdersTbl.Fields("OrderNum")

       CustProdHistTbl.UPDATE

       CustThisOrderTbl.MoveNext

   Wend

To print the report, the Action property of the Crystal Report object is set to 1: 

    ' Kick off the report.

    rptOrderTicket.ReportFileName = "c:\vbproj\sams\ordtick.rpt"

    rptOrderTicket.WindowTitle = "Printing Order Ticket"

    rptOrderTicket.Destination = 0 '1=printer, 0=screen

    rptOrderTicket.Action = 1

After the report is printed (or removed from the screen), the buttons return to normal and the status message is invisible again. Because there is now another order to display, the CustProdHist data control has to be refreshed, which automatically updates the bound OrderHistoryGrid: 

    lblPrntMsg.Visible = False

    btnPrintOrder.Visible = True

    btnDelTickets.Visible = True

    CustProdHist.Refresh

     ...

    Exit Sub

Creating the Order Ticket Report

The major benefit of using a report writer is all the code you don't have to write. The report writer doesn't take care of all the necessary code, but it should handle the pagination, printing, and formatting issues while you deal with the data. The Order Ticket report draws fields from the application database, formats them, and places them on the report.

The header section of the Order Ticket report shows the company name. The first two lines are straight out of the database: CoInf.CompanyName and CoInf.Address1. The user probably entered information in both of these fields. The third and fourth lines, however, may be blank. To avoid printing a blank line if the company does not have a second address line, the third line is a formula field, as shown in Listing 35.11 To create a formula field, select Formula from the Insert item on the Crystal Reports menu bar. When the Crystal Reports editor comes up, you may write Visual Basic-like code that defines what should go in a field (or line) on the report. 

if length({CoInf.Address2}) > 0 then

    {CoInf.Address2}

else

    TrimRight ({CoInf.City}) + ", " + TrimRight ({CoInf.State}) + "    " + {CoInf.Zip}*

The CustOrdMst table drives the order. One entry in the CustOrdMst table generates one report. The customer's name and address come from the CustMain table through a link between it and the CustOrdMst table, as shown in Figure 35.21.


Figure 35.21. Linking the CustOrdMst table to the CustMain table in Crystal Reports.

The detail section of the report lists the items in the customer's order. Through a link to the CustOrdMst table, the report finds all of the entries in the CustOrdDtl table and places them in the detail section of the report.

The bottom of the detail section of the report summarizes the financial information in order by using special summary fields. To create a summary field in Crystal Reports, click on the field you would like to summarize and choose Insert | Summary. The screen shown in Figure 35.22 appears.


Figure 35.22. Creating a summary field in Crystal Reports.

Tip

The following are some tips for making your experience with Crystal Reports (version 3) more pleasant:

Make sure you use fonts that are on the system or include the ones you want when you distribute your application (within copyright restrictions). If your report has a font that is not on the system running it, a substitute (probably Courier) will be used.

Make sure the appropriate .DLLs for Crystal Reports are distributed with your application.

If your application is going to print labels on a dot-matrix printer, your biggest challenge will be the continuous feed. Crystal Reports (version 3) automatically puts a form feed at the end of each page. Compensating for this action is almost not worth it. You may decide to write to the printer object directly and not use a Crystal Report for this feature.

Learn how to use formulas. They allow you to format text and conditionally put values on the report, making it look polished and professional. Several useful ones are included in the reports in this sample application.

Creating the Customer Account Form

The Customer Account form is pretty lean compared to the other forms on the CustInf form. The frmCusta form loads quickly, so the hourglass pointer is a sufficient signal to the user that things are happening. When the form is loaded, the data control is filled with order information about the current customer by setting the RecordSource property with an SQL statement: 

orders.RecordSource = "Select * From Orders Where CustomerNum = " & Str(Customer_number) & " Order By Date DESC;"

The Order By clause sorts the entries by descending date order so the top of the grid will show the most recent activity.

Each time the form is activated or an entry is either added or changed on the Orders grid, the current balance is recalculated and placed in the txtCurrentBalance text field: 

' Calculate and put the current balance on the screen.

Call CalculateCurrentBalance

txtCurrentBalance = CurrentBalance

The CalculateCurrentBalance subroutine is a public subroutine that adds all of the charges and credits in the Orders table for the customer number passed to it. Then this subroutine updates the global variable CurrentBalance and the CustMain table field Current_Balance (see the section on the main module later in this chapter).

Like the Orders form, the Accounts form customer number is implied and therefore not displayed on the grid. To update the database, the customer number has to be added before the record is written. The Orders grid has a BeforeUpdate event routine (see Listing 35.12) that does just this. 

Private Sub OrdersGrid_BeforeUpdate(Cancel As Integer)

     orders.Recordset.Fields("CustomerNum") = Customer_number

End Sub

After the record has been written, the current balance is calculated and the text field showing it is updated, as shown in Listing 35.13. 

Private Sub OrdersGrid_AfterUpdate()

    Call CalculateCurrentBalance

    txtCurrentBalance = CurrentBalance

End Sub

If a record is deleted, the balance has to be recalculated as well: 

Private Sub OrdersGrid_AfterDelete()

    OrdersGrid.SetFocus

    CalculateCurrentBalance

    txtCurrentBalance = CurrentBalance

     ...

If the last record is deleted, then a single record is placed into the grid as a placeholder until the user enters data in the grid: 

    If (orders.Recordset.RecordCount = 0) Then

        orders.Recordset.AddNew

        orders.Recordset.Fields("CustomerNum") = Customer_number

        CalculateCurrentBalance

        OrdersGrid.SetFocus

        Exit Sub

    End If

    orders.Recordset.MoveFirst

    orders.Refresh

Creating the Account Statement Report

The Account Statement is designed to be placed in a windowed envelope. The return address is printed in small fonts on the upper left and the delivery address is in larger type just below it. Several fields in the delivery address imply text like P.O. Box or Suite/Apt #. Formulas in the report determine if there is a value in the P.O. Box field, for example, and then place the implied text in front of the data in the field, as shown in Listing 35.14. 

  TrimRight({BILLING.Address}) + " PO Box " + {BILLING.POBox}

Printing the Account Statement is much like printing the Order report. The main report table is the Billing table. In this report, all of the detail items come from the Orders table, and the detail information comes from the Billing table. Because the Orders table already has all of the information needed for the report, the only table that the application has to update is the Billing table.

Each entry in the Orders table for a customer in the Billing table will get a report. This setup makes it possible to share this report with the other accounting section of this application, the Customer Accounts form.

Creating the frmAccounts Form

The Customer Accounts form (called frmAccounts) shows both summary and detail information about the customers served by this business. Whenever this form is activated, the two grids are populated by the values in the data controls they are bound to and the focus is set to the Orders grid, as shown in Listing 35.15. 

Private Sub Form_Activate()

    ...

    orders.DatabaseName = Database_name

    orders.RecordSource = "Select * from ORDERS Order By CustomerNum, Date DESC;"

    custmain.DatabaseName = Database_name

    custmain.RecordSource = "Select * from CUSTMAIN order by CustomerNum;"

    custmain.Refresh

    orders.Refresh

    ...

End Sub

When the Orders grid has the focus, the CustMain grid gets a cursor put next to the customer being referenced. When a user places the cursor on a field in the Orders grid to look at the details of a customer account, a pointer is put next to the CustMain grid showing the total balance for the account. Listing 35.16 shows the code for these actions. 

Private Sub OrdersGrid_GotFocus()

    ...

    If (orders.Recordset.RecordCount = 0) Then

        ' The following DoEvents is necessary.

        DoEvents

        orders.Recordset.AddNew

        orders.Recordset.Fields("CustomerNum") = Customer_number

    End If

    ' If this is the first time the grid has the focus, populate the billing

    ' fields from an existing record or enter a new record in CustBillAddr.

    If (FirstTimeFocus = True) Then

        Set Tbl = CustDB.OpenTable("CustBillAddr")

        Tbl.Index = "PrimaryKey"

        Tbl.Seek "=", Customer_number

        If (Tbl.NoMatch = True) Then

            Tbl.AddNew

            Tbl.Fields("CustomerNum") = Customer_number

            Tbl.UPDATE

        End If

    End If

End Sub

There is no implied customer number for a record in the customer grid, so the user is required to enter it. When the user adds or changes a record in the Orders grid, the current balance may also change so it must be recalculated, as shown in Listing 35.17. 

Private Sub OrdersGrid_RowColChange(ByVal LastRow As String, ByVal LastCol As Integer)

    ...

    CalculateCurrentBalance (CurrentCustomerNumber)

    ' Update the current balance in CUSTMAIN

    Set CustMainTbl = CustDB.OpenTable("CustMain")

    CustMainTbl.Index = "PrimaryKey"

    CustMainTbl.Seek "=", CurrentCustomerNumber

    If (CustMainTbl.NoMatch = False) Then

        CustMainTbl.Edit

        CustMainTbl.Fields("Current_Balance") = CurrentBalance

        CustMainTbl.UPDATE

        custmain.Refresh

    End If

    ' Synchronize the CustMain grid with the Orders grid

    If orders.Recordset.RecordCount <> 0 Then

        If (IsNull(orders.Recordset.Fields("CustomerNum")) = False) Then

            CurrentCustomerNumber = orders.Recordset.Fields("CustomerNum")

            buffer = "CustomerNum = " & orders.Recordset.Fields("CustomerNum")

            custmain.Recordset.FindFirst buffer

            CustMainGrid.Refresh

        End If

    End If

    ...

End Sub

Similarly, if a user deletes a record from the Orders grid, the current balance has to be recalculated (see Listing 35.18). 

Private Sub OrdersGrid_AfterDelete()

    ...

    CalculateCurrentBalance (Val(Customer_number))

    ...

End Sub

On the CustMain side of the screen, the user may click on a customer number and make the Orders grid cursor move to the first order for that customer (see Listing 35.19). 

Private Sub CustMainGrid_RowColChange(ByVal LastRow As String, ByVal LastCol As Integer)

    ...

    ' Synchronize the Orders grid with the CustMain grid

    If orders.Recordset.RecordCount <> 0 Then

        If custmain.Recordset.RecordCount = 0 Then

            'custmain.Recordset.Refresh

        End If

        If (orders.Recordset.Fields("CustomerNum") <> custmain.Recordset.Fields("CustomerNum")) Then

        ' Only synchronize if records exist in ORDERS for this customer

            Set OrdersTbl = CustDB.OpenTable("Orders")

            OrdersTbl.Index = "CustomerNum"

            OrdersTbl.Seek "=", custmain.Recordset.Fields("CustomerNum")

            If (OrdersTbl.NoMatch = False) Then

                buffer = "CustomerNum = " & custmain.Recordset.Fields("CustomerNum")

                orders.Recordset.FindFirst buffer

            End If

        End If

    End If

End Sub

If there are a lot of customers in the CustMain grid and the user just wants to get to a specific customer quickly, the user can enter the customer number in the CustomerNumberField text box. When the user presses Enter or leaves the field, the Lost Focus event is triggered and the CustMain cursor is reset to the customer number the user typed.

Printing Statements for All Customers

The same report you use to print statements for one person is used to print statements for everyone, but the setup is a little different. You create a snapshot containing customers that have a non-zero balance (there's no point in printing a statement with nothing in it). The billing table is then populated with one record for each of the customers in the snapshot, as shown in Listing 35.20. 

Private Sub Command2_Click()

    ...

    ' Retrieve all records from CUSTMAIN where the current balance is greater

    ' than 0.

    Set RecSet = CustDB.CreateSnapshot("Select * From CUSTMAIN Where Current_Balance > 0.0;")

    ...

    RecSet.MoveFirst

    While (RecSet.EOF = False)

       Screen.MousePointer = 11

       ' Remove any records existing in BILLING

       CustDB.Execute ("Delete * From BILLING")

       BillingTbl.AddNew

       BillingTbl.Fields("CustomerNumber") = RecSet.Fields("CustomerNum")

       BillingTbl.Fields("CompanyName") = RecSet.Fields("Company")

        ...

       BillingTbl.Fields("StatementDate") = Date

       BillingTbl.UPDATE

       rptBilling.ReportFileName = "c:\vbproj\sams\statemnt.rpt"

       rptBilling.Destination = 0    ' 1=Output to Printer, 0=Screen

       rptBilling.Action = 1

       RecSet.MoveNext

       Screen.MousePointer = 0

    Wend

    Exit Sub

billerr:

    MsgBox (Err.Description)

End Sub

When the report is invoked, a new report will be printed for each person in the Billing table.

Note

For the benefit of this demonstration, the report is being sent to the screen. In a real setting, it would make more sense to print to the printer rather than make the user press the Print button for each report. To do this, change the line that sends the output to the screen

rptBilling.Destination = 0 ' 1=Output to Printer, 0=Screen

so that it sends output to the printer

rptBilling.Destination = 1 ' 1=Output to Printer, 0=Screen

Creating the Company Information Form

Both of the reports in the system get information about the customers from the CustMain table and information about the company from the CoInfo table. The Company Information form is about as simple as a data entry form gets in Visual Basic. The Activate procedure populates the CoInfo data control with the records (there is only one) from the CoInfo table: 

Private Sub Form_Activate()

Dim SQLinq

    ' Reset to the default  pointer when returning to this form.

    MousePointer = 0

    SQLinq = "SELECT * FROM COINF;"

    datCoInfo.RecordSource = SQLinq

    datCoInfo.Refresh

    If datCoInfo.Recordset.RecordCount <> 0 Then

        Exit Sub

    Else

        datCoInfo.Recordset.AddNew

        datCoInfo.Recordset.UPDATE

    End If

End Sub

All of the fields on this screen are data-bound text boxes. As the user moves the cursor through the fields on the screen, the data control automatically updates the underlying table. To allow the user to press Enter to move the cursor from one field to another, a form-level Keypress event subroutine translates the Enter key to the Tab key (see Listing 35.21). 

Private Sub Form_KeyPress(KeyAscii As Integer)

    If KeyAscii = 13 Then

        SendKeys "{tab}"

        KeyAscii = 0

    End If

End Sub
Note

You must set the forms Keypreview property to True for this subroutine to work. Otherwise, this code won't be invoked.

The Company Information form is a simple form, but it gives the user the flexibility to change how the company looks on the reports in the system, which is a valuable feature.

Putting Your Signature on the Application with the About Box

The About box is standard in most Windows applications. It is often used to sign your work, make legal statements, and provide general information about the application.

An About box can be as simple as a small form with your name and the name of the application. If your client calls you with questions or problems, it can be helpful to include the version number and system information (like available memory or disk space) on this screen, too.

Creating the Main Module

There is a lot less code in a Visual Basic application than there is in other languages, but you will still need some plain old-fashioned code. The MAIN.BAS module is where you set global variables and enter general subroutines and functions (see Listing 35.22). You should try to minimize your use of global variables because they take up memory and resources. But if you find that you are passing the same variable around, consider making that variable global. 

' Allow the user to set the database location

Global Database_name

Global CustDB As DATABASE

' Snapshot for Custmain table

Global CustmainSnapshot As Snapshot

Global CustmainDynaset As Dynaset

' Customer number is the key to the database

Global Customer_number

' Customer balance

Global CurrentBalance As Double

The subroutines in the MAIN.BAS are general functions that can be called by subroutines in one or more forms.

The CalculateCurrentBalance() is a subroutine that is called from the Customer Account form and the General Accounting form. It is the single place that the customer balance is calculated and the only routine that changes the CurrentBalance global variable. It is also the only routine that changes the Current_Balance field in the CustMain table. All this is accomplished with just a few lines of code and some SQL. A single SQL statement accesses the Orders table in the database, adds up the charges and the credits for the current customer, and puts all the information into a snapshot (see Listing 35.23). 

Public Sub CalculateCurrentBalance()

    Dim RecSet As Snapshot

    Dim buffer As String

    Dim CustOrdTbl As TABLE

    Dim charge

    Dim credit

    Dim Tbl As TABLE

    ' Get all of the customers orders and subtract

    ' the credits from the charges.

    Set CustOrdTbl = CustDB.OpenTable("Orders")

    buffer = "Select Sum(Charge),Sum(Credit) From Orders Where CustomerNum = " & Str(Customer_number) & ";"

    Set RecSet = CustDB.CreateSnapshot(buffer)

    ' If there is no value, then the value is considered zero.

    If (IsNull(RecSet.Fields(0))) = True Then

        charge = 0

    Else

        charge = RecSet.Fields(0)

    End If

    If (IsNull(RecSet.Fields(1))) = True Then

        credit = 0

    Else

        credit = RecSet.Fields(1)

    End If

    ' Set the gloabal variable

    CurrentBalance = charge - credit

    ' close the order table

    CustOrdTbl.Close

    ' Now update the main customer table.

    Set Tbl = CustDB.OpenTable("CustMain")

    Tbl.Index = "PrimaryKey"

    Tbl.Seek "=", Customer_number

    If (Tbl.NoMatch = False) Then

        Tbl.Edit

        Tbl.Fields("Current_Balance") = CurrentBalance

        Tbl.UPDATE

    End If

    Tbl.Close

End Sub

Running and Testing the Application

Most developers tend to run the application as pieces get developed. By the time all the forms and reports are completed and the database looks like it is getting updated properly, the application is probably fairly stable. A quick last test is to create an executable file. Visual Basic programs run differently as executable files than they do when they are interpreted in the development environment. The process of creating an executable file demonstrates some errors that do not occur in the interpreted world. To create an executable version of your application, select the MAKE.EXE file item from the File option on the menu bar.

Tip

The following are some tips on debugging and testing:

Turn off error handling when debugging a procedure (On Error Goto 0) so that Visual Basic can tell you what it thinks the problem is.

Set a breakpoint several lines before the line of code that is causing the error, and add a watch for what is causing the problem. Anything you write in Visual Basic can be watched, even database field values in a recordset.

If you suspect a corrupt database is the cause of your problem, use the Repair Database option from the Data Manager's File menu. Sometimes the Compact Database option can help, too.

Save early; save often. Always back up what you have before trying to fix a bug. There is nothing quite as demoralizing as working on a problem only to find you've made things worse and can't even get back to what was bad in the first place. At the very least, copy all of your .FRM files to a safe place. If you need to bring back an old version of a form, you can copy it back.

Refer to Chapter 13 for a more thorough discussion about debugging techniques.

All source and project files for the 16-bit version of this application are on the CD-ROM included with this book. They are intended for learning purposes only. The sample project for this chapter was developed using the 16-bit Visual Basic programming environment under Windows 3.1.

Summary

In this chapter, you learned what questions to ask when developing an application for the user. You learned how to design a database and develop a functional prototype. You also learned how to code and test an application. Finally, you discovered the best way to test and run an application.

Previous Page TOC Next Page